﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics.CodeAnalysis;

namespace Workbench.Persistance
{
    public class DbUtility
    {
        public static string GetConnectionString(string projectDbPath)
        {
            return new SQLiteConnectionStringBuilder() { DataSource = projectDbPath }.ToString();
        }

        public static void CreateDatabase(string projectDbFilePath)
        {
            SQLiteConnection.CreateFile(projectDbFilePath);
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static int ExecuteNonQuery(SQLiteConnection con, string commandText)
        {
            using (var cmd = new SQLiteCommand(commandText, con))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static int ExecuteNonQuery(SQLiteConnection con, string commandText, IDictionary<string, object> parameters)
        {
            using (var cmd = new SQLiteCommand(commandText, con))
            {
                foreach(var param in parameters)
                {
                    cmd.Parameters.AddWithValue(param.Key, param.Value);
                }

                return cmd.ExecuteNonQuery();
            }
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static object ExecuteScalar(SQLiteConnection con, string commandText)
        {
            using (var cmd = new SQLiteCommand(commandText, con))
            {
                return cmd.ExecuteScalar();
            }
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static T ExecuteScalar<T>(SQLiteConnection con, string commandText)
        {
            using (var cmd = new SQLiteCommand(commandText, con))
            {
                var result = cmd.ExecuteScalar();
                if (result == DBNull.Value) return default(T);
                return (T)result;
            }
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static T ExecuteScalar<T>(SQLiteConnection con, string commandText, IDictionary<string, object> parameters)
        {
            using (var cmd = new SQLiteCommand(commandText, con))
            {
                foreach (var param in parameters)
                {
                    cmd.Parameters.AddWithValue(param.Key, param.Value);
                }

                return ExecuteScalar<T>(cmd);
            }
        }

        public static T ExecuteScalar<T>(SQLiteCommand cmd)
        {
            var result = cmd.ExecuteScalar();
            if (result == DBNull.Value || result == null) return default(T);
            return (T)result;
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static SQLiteDataReader ExecuteReader(SQLiteConnection con, string cmdText)
        {
            using (var cmd = new SQLiteCommand(cmdText, con))
            {
                return cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            }
        }

        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static SQLiteDataReader ExecuteReader(SQLiteConnection con, string cmdText, IDictionary<string, object> parameters)
        {
            using (var cmd = new SQLiteCommand(cmdText, con))
            {
                foreach (var param in parameters)
                {
                    cmd.Parameters.AddWithValue(param.Key, param.Value);
                }

                return cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            }
        }


        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public static void ExecuteReader(SQLiteCommand cmd, Action<SQLiteDataReader> action)
        {
            var rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            while (rdr.Read()) action(rdr);
        }


        public static object GetForeignKeyValue(long foreignKeyId)
        {
            return foreignKeyId != 0 ? (object)foreignKeyId : DBNull.Value;
        }

        public static SQLiteConnection GetConnection(string connectionString)
        {
            var connection = new SQLiteConnection(connectionString);
            connection.Open();
            DbUtility.ExecuteNonQuery(connection, @"PRAGMA foreign_keys = ON;");
            return connection;
        }
    }
}
